import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JSqlParserAddWhereUtil {

    private static final String AND = "and";
    private static final String OR = "or";

    /**
     * 表别名映射关系
     */
    private static Map<String, String> TABLE_AND_ALIAS_MAPPING = new HashMap<>();

    /**
     * where 条件
     */
    private static Map<String, String> WHERE_MAPPING = new HashMap<>();


    public static void main(String[] args) throws JSQLParserException {
        //1、获取原始sql输入
//        String sql = "select t1.s_id as id," +
//                "t1.s_name," +
//                "t1.join_date, \n" +
//                "t2.score, \n" +
//                "t2.* \n" +
//                "from schema1.edu_college_student t1\n" +
//                "join schema2.edu_college_test_score t2\n" +
//                "on t2.s_id = t1.s_id \n" +
//                "where 1=1 \n";

//        String sql = "select t11.*,t1.* \n" +
//                "from original_data.edu_college_student As t1\n" +
//                "JOIN original_data.edu_college_test_score t11\n" +
//                "on t1.s_id = t11.s_id \n" +
//                "where 1=1 \n";

//        String sql = "select t1.*,t1.*,t2.*\n" +
//                "from edu_college_student t1\n" +
//                "join edu_college_test_score t2 on t2.s_id = t1.s_id";

//        String sql = "select '1' from meta_dict_type";

//        String sql = "select\n" +
//                "  t1.s_id,\n" +
//                "  t1.s_name,\n" +
//                "  max(t2.score) as maxscore,\n" +
//                "  t2.course\n" +
//                "from\n" +
//                "  original_data.edu_college_student t1\n" +
//                "  join original_data.edu_college_test_score t2 on t2.s_id = t1.s_id\n" +
//                "group by\n" +
//                "  t2.course,\n" +
//                "  t1.s_id,\n" +
//                "  t1.s_name";

//        String sql = "select t2.id from (select t1.id from (select id from original_data.edu_college_student) t1) t2";

//        String sql = "select t1.stime,t1.sscore from (select o.create_time as stime,t.score as sscore from original_data.edu_college_student o join original_data.edu_college_test_score t on t.s_id = o.s_id ) t1";

//        String sql = "select t1.s_id as sid, t1.t1.s_name from original_data.edu_college_student t1";

        String sql = "select\n" +
                "  v1.id as t_id,\n" +
                "  v1.s_name as t_s_name,\n" +
                "  v1.join_date as t_date,\n" +
                "  v1.score As t_score,\n" +
                "  t3.course AS t_course\n" +
                "from\n" +
                "  (\n" +
                "    select\n" +
                "      t1.s_id as id,\n" +
                "      t1.s_name,\n" +
                "      t1.join_date,\n" +
                "      t2.score\n" +
                "    from\n" +
                "      original_data.edu_college_student t1\n" +
                "      join original_data.edu_college_test_score t2 on t2.s_id = t1.s_id\n" +
                "    where\n" +
                "      1 = 1\n" +
                "  ) v1\n" +
                "  join original_data.edu_college_sign_in_situation t3 on t3.s_id = v1.id\n" +
                "  join (select id,b,c from Test) t4 on v1.id = t4.id\n" +
                "  limit 10";

        try {
            mergeSql(sql.replaceAll("[\r\n]", " "), WHERE_MAPPING);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取SQL结构
     *
     * @param sql
     * @throws JSQLParserException
     */
    public synchronized static String mergeSql(String sql, Map<String, String> whereMap) throws JSQLParserException, ServiceException {
        if (StringUtils.isEmpty(sql)) {
            throw new ServiceException("请先输入SQL语句");
        }
        WHERE_MAPPING = whereMap;
        sql = sql.replaceAll("(\\$\\{\\w*\\})|(\\{\\{\\w+\\}\\})", "''").toLowerCase();
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        analysisSql(select.getSelectBody());

        System.out.println("【END】" + sql);
        System.out.println("【END】" + select);
        WHERE_MAPPING.clear();
        return select.toString();
    }

    /**
     * 解析SQL
     *
     * @param selectBody         SQL语句
     * @throws JSQLParserException
     */
    private static void analysisSql(SelectBody selectBody) throws JSQLParserException {
        PlainSelect plainSelect;
        if (selectBody instanceof PlainSelect) {
            plainSelect = (PlainSelect) selectBody;
            analysisWhere(plainSelect);
        } else if (selectBody instanceof SetOperationList) {
            List<SelectBody> selectBodies = ((SetOperationList) selectBody).getSelects();
            for (SelectBody subSelectBody : selectBodies) {
                plainSelect = (PlainSelect) subSelectBody;
                analysisWhere(plainSelect);
            }
        }
    }

    private static void analysisWhere(PlainSelect plainSelect) throws JSQLParserException {
        // 1.解析from（可能含有子查询）
        FromItem fromItem = plainSelect.getFromItem();
        getFromItem(fromItem);

        // 2.解析join
        getJoinItem(plainSelect);

        // 3.解析WHERE条件   关键词AND，OR或者NOT来表达多个复合查询条件。
        getWhere(plainSelect);
    }

    /**
     * 获取from的项目
     *
     * @param fromItem
     * @return
     * @throws JSQLParserException
     */
    private static void getFromItem(FromItem fromItem) throws JSQLParserException {
        // 判断fromItem属于哪种类型，如果是subSelect类型就是子查询
        if (fromItem instanceof SubSelect) {
            SelectBody selectBody = ((SubSelect) fromItem).getSelectBody();
            analysisSql(selectBody);
        } else if (fromItem instanceof Table) {
            Table table = ((Table) fromItem);
            String tableName = table.getName().toLowerCase();
            String alias;
            if (ObjectUtils.isNotEmpty(table.getAlias())) {
                alias = table.getAlias().toString().trim().toLowerCase();
            } else {
                alias = tableName;
            }
            TABLE_AND_ALIAS_MAPPING.put(tableName, alias);
        }
    }

    /**
     * 获取join的项目
     *
     * @param plainSelect
     * @return
     */
    private static void getJoinItem(PlainSelect plainSelect) throws JSQLParserException {
        List<Join> joinList = plainSelect.getJoins();
        if (joinList != null) {
            for (Join join : joinList) {
                if (join.getRightItem() instanceof Table) {
                    Table table = (Table) join.getRightItem();
                    String tableName = table.getName().toLowerCase();
                    String alias = table.getAlias().toString().trim().toLowerCase();
                    TABLE_AND_ALIAS_MAPPING.put(tableName, alias);
                } else if (join.getRightItem() instanceof SubSelect) {
                    analysisSql(((SubSelect) join.getRightItem()).getSelectBody());
                }
            }
        }
    }

    private static void getWhere(PlainSelect plainSelect) throws JSQLParserException {
        if (MapUtils.isNotEmpty(WHERE_MAPPING) && MapUtils.isNotEmpty(TABLE_AND_ALIAS_MAPPING)) {
            TABLE_AND_ALIAS_MAPPING.keySet().retainAll(WHERE_MAPPING.keySet());
            if (MapUtils.isNotEmpty(TABLE_AND_ALIAS_MAPPING)) {
                String whereStr = "";
                for (String tableName : TABLE_AND_ALIAS_MAPPING.keySet()) {
                    whereStr += WHERE_MAPPING.get(tableName).replace("?", TABLE_AND_ALIAS_MAPPING.get(tableName));
                }
                Expression where;
                if (plainSelect.getWhere() != null) {
                    where = CCJSqlParserUtil.parseCondExpression(plainSelect.getWhere().toString() + whereStr);
                } else {
                    if (whereStr.startsWith(AND)) {
                        whereStr = whereStr.replaceFirst(AND, "");
                    } else if (whereStr.startsWith(OR)) {
                        whereStr = whereStr.replaceFirst(OR, "");
                    }
                    where = CCJSqlParserUtil.parseCondExpression(whereStr);
                }
                plainSelect.setWhere(where);
                TABLE_AND_ALIAS_MAPPING.clear();
            }
        }
    }
}
